/*
 * DaoService
 * 
 * Version 1.0
 *
 * 31-07-08
 * 
 * Modification Logs:
 *   DATE        AUTHOR      DESCRIPTION
 *  --------------------------------------------------------
 *  31-07-08	TuHA		Tao lop
 *  21-08-08	TuHA		Chinh lop
 *  28-08-08	TuHA		modify constructor
 */

package vn.fpt.fsoft.fcos.dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 
 * @author TuHA
 * @version 1.0
 */
public class DaoService {

	/**
	 * Doi tuong ket noi Connection
	 */
	private Connection conn;

	/**
	 * @return the current connection
	 */
	public Connection getConnection() {
		return conn;
	}

	/**
	 * @param conn is the Connection object to set
	 */
	public void setConnection(Connection conn) {
		this.conn = conn;
	}
	
	
	public DaoService() {
		//tao ket noi
		Properties props = new Properties();
		String connectionString = "";
		
		try {
			
			// read file properties
			FileInputStream fileIn = new FileInputStream("database.xml");
			props.loadFromXML(fileIn);
			
			// load driver for sql server 2005
			Class.forName(props.getProperty("driver"));

			// declare url string
			connectionString = props.getProperty("url");
		         					
			conn = DriverManager.getConnection (connectionString, props);
			
		} catch(SQLException e) {
			
		} catch(ClassNotFoundException e) {
			
		} catch(FileNotFoundException e) {
			
		} catch(IOException e) {
			
		}
	}
	
	/**
	 * commit method commit the transaction
	 */
	public void commit(){
		try{
			conn.commit();	
		}catch (SQLException exception){
			
		}
		
	}
	
	/**
	 * rollback method rollback the transaction
	 */
	public void rollback(){
		try{
			conn.rollback();	
		}catch(SQLException exception){
			
		}
		
	}
	
	/**
	 * buildSQL build an statement sql
	 * @param conn Connection object for create statement
	 * @param sqlTemplate is the template of this statement
	 * @param map is the map table of parameter name and its value
	 * @return PreparedStatement object
	 */
	public static PreparedStatement buildSQL(Connection conn, 
											String sqlTemplate,
											Map<String, Object> map){
		String COLUMN_NAME_PATTERN = "[\\x24][\\{]([a-zA-Z0-9_]+)[\\}]";
		Pattern pattern = Pattern.compile(COLUMN_NAME_PATTERN);
        Matcher matcher = pattern.matcher(sqlTemplate);
        StringBuffer sb = new StringBuffer();
        final String sqlStatement = ""; // the standard query with question mark as parameters     
        PreparedStatement pstmt = null;
        int index = 0;
        String key;
        Object objVal;
        
		try{
			
	        
	        // Find column name
	        while (matcher.find()) {
	        	
	            // replace the column name pattern by question mark
	            matcher.appendReplacement(sb, "?");
	        }
	        
	        // append the tail of the query template to the String buffer
	        matcher.appendTail(sb);
	        matcher.reset();

	        pstmt = conn.prepareStatement(sb.toString());
	        // the while loop can be replaced by for loop for performance        
	        while (matcher.find()) {
	            key = matcher.group(1);
	            objVal = map.get(key);

	            if (objVal == null) {
	                pstmt.setString(++index, null);
	            } else {
	                if (objVal instanceof Integer) {
	                    pstmt.setInt(++index, (Integer) objVal);

	                } else if (objVal instanceof java.util.Date) {
	                    java.sql.Date sqlDate = new java.sql.Date(((Date) objVal).getTime());
	                    pstmt.setDate(++index, sqlDate);

	                } else if (objVal instanceof String) {
	                    pstmt.setString(++index, objVal.toString());

	                } else { // unknown type of the value
	                    throw new SQLException("Unknown type of the value '" + objVal + "'");
	                }
	            }
	        }
		}
		catch (SQLException e) {
			pstmt = null;
		}
		
        return pstmt;
	}
		
	/**
	 * executeSelect execute the query of conn Connection
	 * @param conn Connection object for create statement
	 * @param sql the query string is executed
	 * @return the ResultSet object of this query
	 */
	public static ResultSet executeSelect(Connection conn, String sql){
		
		// the result of this query string
		ResultSet resultSet = null;
		
		// the prepare statement for executing this query
		PreparedStatement pstm = null;
		
		try{
			// create statement object
			pstm = conn.prepareStatement(sql);
			
			// query
			resultSet = pstm.executeQuery();
		} catch (SQLException e) {
			
		}
		
		// return the result of this query
		return resultSet;
	}
	
	/**
	 * executeSelect execute the query of a new connection
	 * @param sql the query string is executed
	 * @return the ResultSet object of this query
	 */
	public static ResultSet executeSelect(String sql){
		DaoService service = new DaoService();
		
		Connection conn = service.getConnection();
		
		return executeSelect(conn, sql);
	}
	
	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub
		DaoService service = new DaoService();
		int i = 0;
		
		i++;
	}


}
